<?php

// $Id$

require_once(TANGRA_MAIN_DIR.'interfaces/i_db_storable.class.php');


require_once('tcms_ci_image.class.php');
require_once('tcms_ci_dbc.class.php');

class TCMS_CI_Image_DBC extends TCMS_ci_Image implements I_DB_Storable {
	public function save(DB_Connection $dbc) {
		$ret = false;

		if ($this->get_id()) {
			$ret = $this->update($dbc);
		} else {
			$ret = $this->insert($dbc);
			$this->set_id($ret);
		}

		return $ret;
	}


	public function load_by_id(DB_Connection $dbc, $id) {
		tangra_if_not_int_throw_e($id);

		$ret = false;

		$sql = "select ci, image, ordering, alt, note from tcms_ci_images where id = $id";
		$rez = $dbc->execute($sql);

		if (!$rez->is_eof()) {
			$rez_obj = $rez->fetch_object();

			$this->set_id($id);
			$this->set_ci($rez_obj->CI);
			$this->set_image($rez_obj->IMAGE);
			$this->set_ordering($rez_obj->ORDERING);
			$this->set_alt(stripslashes($rez_obj->ALT));
			$this->set_note(stripslashes($rez_obj->NOTE));

			$ret = $id;
		}

		return $ret;
	}


	protected function insert(DB_Connection $dbc) {
		$ret = false;

		$id = $dbc->generate_id('tcms_ci_images_seq');
		$ci = $this->get_ci();
		$image = $this->get_image();
		$ordering = $this->get_ordering();
		$alt = addslashes($this->get_alt());
		$note = addslashes($this->get_note());

		if ($id) {
			$sql = "insert into tcms_ci_images (id, ".
									      "ci, ".
									      "image, ".
									      "ordering, ".
									      "alt, ".
									      "note ".
									      ") ".
								"values ".
									      "($id,".
									      "$ci, ".
									      "$image, ".
									      "$ordering, ".
									      "'$alt', ".
									      "'$note' ".
									      ")";
			$dbc->execute($sql);
			$ret = $id;
		} else {
			throw new TE_Exception('tcms_ci_images_seq');
		}

		return $ret;
	}


	protected function update(DB_Connection $dbc) {
		$id = $this->get_id();

		$ci = $this->get_ci();
		$image = $this->get_image();
		$ordering = $this->get_ordering();
		$alt = addslashes($this->get_alt());
		$note = addslashes($this->get_note());


		$sql = "update tcms_ci_images set ".
						"ci = $ci, ".
						"image = $image, ".
						"ordering = $ordering, ".
						"alt = '$alt', ".
						"note = '$note' ".
					"where id = ".$id;

		$dbc->execute($sql);
		$ret = $id;

		return $ret;
	}


	public static function delete(DB_Connection $dbc, $id) {
		tangra_if_not_int_throw_e($id);

		$sql = "delete from tcms_ci_images where id = $id";
		$dbc->execute($sql);
	}


	public static function get_sql_for_grid($ci_id) {
		$sql = "select i.id, "
						."i.orig_filename, "
						."i.size, "
						."i.size_x, "
						."i.size_y, "
						."i.filename, "
						."i.note, "
						."i.alt, "
						."ci.id, "
						."ci.ordering, "
						."ci.alt, "
						."ci.note, "
						."ci.ci "
					."from tcms_ci_images ci, tcms_images i "
					."where ci.ci = $ci_id "
						." and ci.image = i.id "
					."order by ci.ordering asc";

		return $sql;
	}


	public static function	get_sql_count_for_grid($ci_id) {
		$sql = "select count(id) as total_rows from tcms_ci_images where ci = $ci_id";

		return $sql;
	}


	public static function get_sql_for_cis_grid($ci_id, $filename, $notealt) {
		$where = self::prepare_where_clause($filename, $notealt);
		$sql = "select i.id, "
						."i.orig_filename, "
						."i.size, "
						."i.size_x, "
						."i.size_y, "
						."i.filename, "
						."i.note, "
						."i.alt, "
						."ci.id, "
						."ci.ordering, "
						."ci.alt, "
						."ci.note "
					."from tcms_images i left outer join tcms_ci_images ci on (i.id = ci.image and ci.ci = $ci_id) ".$where.' order by i.id desc';;


		return $sql;
	}


	public static function get_sql_count_for_cis_grid($filename, $notealt) {
		$where = self::prepare_where_clause($filename, $notealt);
		$sql = "select count(id) as total_rows from tcms_images i ".$where;

		return $sql;
	}


	private static function prepare_where_clause($filename, $notealt) {
		$filename = self::remove_duplicate_asteriks($filename);
		$notealt = self::remove_duplicate_asteriks($notealt);

		if ($filename || $notealt) {
			$where = '';
			if ($filename) {
				if (strpos($filename, '*') !== false) {
					$filename = addslashes($filename);
					$filename = str_replace('*', '%', $filename);
					$where .= "i.orig_filename like '$filename' ";
				} else {
					$filename = addslashes($filename);
					$where .= "i.orig_filename = '$filename' ";
				}
			}

			if ($notealt) {
				if (strpos($notealt, '*') !== false) {
					$notealt = addslashes($notealt);
					$notealt = str_replace('*', '%', $notealt);
					if ($where) {
						$where .= "and ";
					}
					$where .= "(i.note like '$notealt' or i.alt like '$notealt')";
				} else {
					if ($where) {
						$where .= "and ";
					}
					$notealt = addslashes($notealt);
					$where .= "(i.note = '$notealt' or i.alt = '$notealt')";
				}
			}
		} else {
			$where = '';
		}

		if ($where) {
			$where = " where ".$where;
		}

		return $where;
	}


	private static function remove_duplicate_asteriks($filter) {
		$ret = $filter;
		while(strpos($ret, '**') !== false) {
			$ret = str_replace('**', '*', $ret);
		}

		return $ret;
	}


	public static function exists(DB_Connection $dbc, $ci_id, $image_id) {
		tangra_if_not_int_throw_e($ci_id);
		tangra_if_not_int_throw_e($image_id);

		$sql = "select id from tcms_ci_images where ci = $ci_id and image = $image_id";
		$rez = $dbc->execute($sql);

		return !$rez->is_eod();
	}


	public static function select_ci_ids_and_type_for_image(DB_Connection $dbc, $image_id) {
		tangra_if_not_int_throw_e($image_id);

		$ret = array();

		$sql = "select c.id as id, c.type as type from tcms_cis c, tcms_ci_images cim where cim.image = $image_id and cim.ci = c.id";
		$rez = $dbc->execute($sql);

		while($rez_obj = $rez->fetch_object()) {
			$ret[$rez_obj->ID] = $rez_obj->TYPE;
		}

		return $ret;
	}


	public static function load_ci_images(DB_Connection $dbc, $ci_id) {
		tangra_if_not_int_throw_e($ci_id);

		$ret = array();

		$sql = "select id from tcms_ci_images where ci = $ci_id";

		$rez = $dbc->execute($sql);
		while($rez_obj = $rez->fetch_object()) {
			$tmp = new TCMS_CI_Image_DBC();
			$tmp->load_by_id($dbc, $rez_obj->ID);

			$ret[] = clone $tmp;
		}

		return $ret;
	}


	public static function delete_for_image(DB_Connection $dbc, $image_id) {
		tangra_if_not_int_throw_e($image_id);

		$sql = "select id, ci from tcms_ci_images where image = $image_id";
		$rez = $dbc->execute($sql);
		while ($rez_obj = $rez->fetch_object()) {
			TCMS_CI_Image_DBC::delete($dbc, $rez_obj->ID);
			TCMS_CI_Image_DBC::recalc_ordering($dbc, $rez_obj->CI);
		}

		$dbc->execute($sql);
	}


	public static function delete_for_ci(DB_Connection $dbc, $ci_id) {
		tangra_if_not_int_throw_e($ci_id);

		$sql = "delete from tcms_ci_images where ci = $ci_id";
		$dbc->execute($sql);
	}


	public static function get_max_ordering(DB_Connection $dbc, $ci_id) {
		tangra_if_not_int_throw_e($ci_id);

		$sql = "select max(ordering) as m from tcms_ci_images where ci = $ci_id";
		$rez = $dbc->execute($sql);
		$rez_obj = $rez->fetch_object();

		return $rez_obj->M;
	}



	public static function get_next_ordering(DB_Connection $dbc, $ci_id) {
		return TCMS_CI_Image_DBC::get_max_ordering($dbc, $ci_id) + 1;
	}


	public static function recalc_ordering(DB_Connection $dbc, $ci_id) {
		tangra_if_not_int_throw_e($ci_id);

		$sql = "select id from tcms_ci_images where ci = $ci_id";
		$rez = $dbc->execute($sql);
		while ($rez_obj = $rez->fetch_object()) {
			$arr[] = $rez_obj->ID;
		}

		$c = 1;
		foreach($arr as $id) {
			$sql = "update tcms_ci_images set ordering = $c where id = $id";
			$rez = $dbc->execute($sql);
		}
	}


	public static function move_in_ordering(DB_Connection $dbc, $ci_id, $ci_img_id, $move_to) {
		tangra_if_not_int_throw_e($ci_id);
		tangra_if_not_int_throw_e($ci_img_id);
		tangra_if_not_int_throw_e($move_to);

		$max = TCMS_CI_Image_DBC::get_max_ordering($dbc, $ci_id);
		if ($move_to <= $max && $move_to >= 1) {
			$ci_img = new TCMS_CI_Image_DBC();
			if ($ci_img->load_by_id($dbc, $ci_img_id)) {
				if ($move_to > $ci_img->get_ordering()) {
					$direction = 1;
				} elseif($move_to < $ci_img->get_ordering()) {
					$direction = -1;
				} else {
					return;
				}

				$arr = array();
				$sql = "select id, ordering from tcms_ci_images where ci = $ci_id order by ordering asc";
				$rez = $dbc->execute($sql);
				while ($rez_obj = $rez->fetch_object()) {
					if ($rez_obj->ID != $ci_img_id) {
						$arr[$rez_obj->ID] = $rez_obj->ORDERING * 10;
					}
				}

				$arr[$ci_img_id] = $move_to*10 + $direction;
				asort($arr);

				$c = 1;
				foreach($arr as $id => $ordering) {
					$sql = "update tcms_ci_images set ordering = $c where id = $id";
					$dbc->execute($sql);
					$c++;
				}
			}
		}
	}
}